/*******************************************************************************
"SCARCITY WITHOUT LEVIATHAN: The Violent Effects of Cocaine Supply Shortages
in the Mexican Drug War"
J.C. Castillo, D. Mejia, P. Restrepo
*********************************************************************************

This do file produces:
TABLE 2: "Time series effects of cocaine seizures in Colombia on violence in Mexico"

The table presents estimates of the effect of cocaine seizures in Colombia
on violence in different groups of municipalities in Mexico.

Dependent variables are:
- homicide rate
- drug-related homicide rate
- assassinations of cartel members
- confrontations deaths
- assassinations of government officials

Before running: set the path to current folder at line 29
*******************************************************************************/


clear all
set matsize 5000
set more off

*Set local folder
local folder /*SET THE PATH TO CURRENT FOLDER HERE*/

*Set working directory
cd `folder'
use dta\CastilloMejiaRestrepo.dta

tab year if  timeTS >= ym(2006,12), gen(yy)

*mexican seasons
gen rainy=(month>=5 & month<=9)
gen hurricane=((month>=6 & month<=11))

*us seasons
gen spring=(month>=3 & month<=5)
gen summer=(month>=6 & month<=8)
gen fall=(month>=9 & month<=11)

*quarters
gen quart1=(month>=1 & month<=3)
gen quart2=(month>=4 & month<=6)
gen quart3=(month>=7 & month<=9)
gen quart4=(month>=10 & month<=12)

*month effects
tab month, gen(mm)

*time series polinomial and year FE
local tscontrols t t2 t3  yy2 yy3 yy4 yy5 

gen supplyShock=log(cocaincCol)

******************************************************************
* Table 2, panel 1 - Dep Var: homicide rate   
******************************************************************

//Columns 1,2,3 - All of Mexico
*******************************
preserve
gen homicides=homSIMBAD

*IGAE=Indice Global de Actividad Economics
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e1

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e2

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e3
restore

//Columns 4,5,6 - Quintiles 1 & 2
*******************************
preserve
gen homicides=homSIMBAD
keep if q12==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e4

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e5

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e6
restore

//Columns 7,8,9 - First Quintile
*******************************
preserve
gen homicides=homSIMBAD
keep if q1==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e7

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e8

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e9
restore

estout e1 e2 e3 e4 e5 e6 e7 e8 e9 using Tables/Table2pan1.tex, style(tex) varlabels(supplyShock "log of seizures in Colombia") cells(b(star fmt(%9.3f)) se(par fmt(%9.3f))) stats(N r2 , fmt(%7.0f  %7.3f) labels("Observations" "R-squared"))  collabels(none)  keep(supplyShock) order(supplyShock)  starlevels(\$^{*}\$ .1 \$^{**}\$ .05 \$^{***}\$ .01) nolabel replace mlabels(none)

******************************************************************
* Table 2, panel 2 - Dep Var: drug-related homicide rate  
******************************************************************

//Columns 1,2,3 - All of Mexico
*******************************
preserve
gen homicides=tothomicideB
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e1

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e2

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e3
restore

//Columns 4,5,6 - Quintiles 1 & 2
*******************************
preserve
gen homicides=tothomicideB
keep if q12==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e4

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e5

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e6
restore

//Columns 7,8,9 - First Quintile
*******************************
preserve
gen homicides=tothomicideB
keep if q1==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e7

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e8

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e9
restore

estout e1 e2 e3 e4 e5 e6 e7 e8 e9 using Tables/Table2pan2.tex, style(tex) varlabels(supplyShock "log of seizures in Colombia") cells(b(star fmt(%9.3f)) se(par fmt(%9.3f))) stats(N r2 , fmt(%7.0f  %7.3f) labels("Observations" "R-squared"))  collabels(none)  keep(supplyShock) order(supplyShock)  starlevels(\$^{*}\$ .1 \$^{**}\$ .05 \$^{***}\$ .01) nolabel replace mlabels(none)

******************************************************************
* Table 2, panel 3 - Dep Var: assassinations of cartel members 
******************************************************************
//Columns 1,2,3 - All of Mexico
*******************************
preserve
gen homicides=ejehomicideB
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e1

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e2

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e3
restore

//Columns 4,5,6 - Quintiles 1 & 2
*******************************
preserve
gen homicides=ejehomicideB
keep if q12==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e4

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e5

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e6
restore


//Columns 7,8,9 - First Quintile
*******************************
preserve
gen homicides=ejehomicideB
keep if q1==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e7

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e8

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e9

restore

estout e1 e2 e3 e4 e5 e6 e7 e8 e9 using Tables/Table2pan3.tex, style(tex) varlabels(supplyShock "log of seizures in Colombia") cells(b(star fmt(%9.3f)) se(par fmt(%9.3f))) stats(N r2 , fmt(%7.0f  %7.3f) labels("Observations" "R-squared"))  collabels(none)  keep(supplyShock) order(supplyShock)  starlevels(\$^{*}\$ .1 \$^{**}\$ .05 \$^{***}\$ .01) nolabel replace mlabels(none)

******************************************************************
* Table 2, panel 4 - Dep Var: confrontations deaths 
******************************************************************

//Columns 1,2,3 - All of Mexico
*******************************
preserve
gen homicides=enfhomicideB
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e1

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e2

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e3
restore

//Columns 4,5,6 - Quintiles 1 & 2
*******************************
preserve
gen homicides=enfhomicideB
keep if q12==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e4

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e5

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e6

restore


//Columns 7,8,9 - First Quintile
*******************************
preserve
gen homicides=enfhomicideB
keep if q1==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e7

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e8

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e9

restore


estout e1 e2 e3 e4 e5 e6 e7 e8 e9 using Tables/Table2pan4.tex, style(tex) varlabels(supplyShock "log of seizures in Colombia") cells(b(star fmt(%9.3f)) se(par fmt(%9.3f))) stats(N r2 , fmt(%7.0f  %7.3f) labels("Observations" "R-squared"))  collabels(none)  keep(supplyShock) order(supplyShock)  starlevels(\$^{*}\$ .1 \$^{**}\$ .05 \$^{***}\$ .01) nolabel replace mlabels(none)


*********************************************************************
* Table 2, panel 5 - Dep Var: assassinations of government officials
*********************************************************************

//Columns 1,2,3 - All of Mexico
*******************************
preserve
gen homicides=agrehomicideB
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e1

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e2

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e3

restore

//Columns 4,5,6 - Quintiles 1 & 2
*******************************
preserve
gen homicides=agrehomicideB
keep if q12==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e4

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e5

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e6

restore

//Columns 7,8,9 - First Quintile
*******************************
preserve
gen homicides=agrehomicideB
keep if q1==1
collapse `tscontrols' supplyShock unemp IGAE rainy hurricane (sum)  homicides poblacion, by(timeTS)

/*Generate annualized homicide rate*/
gen tasa = homicides/poblacion * 100000 * 12
gen log_tasa=log(tasa)	
tsset timeTS

reg log_tasa supplyShock `tscontrols' if  timeTS >= ym(2006,12), robust
estimates store e7

reg log_tasa supplyShock `tscontrols' unemp IGAE  if  timeTS >= ym(2006,12), robust
estimates store e8

reg log_tasa supplyShock `tscontrols' unemp IGAE  rainy hurricane if  timeTS >= ym(2006,12), robust
estimates store e9

restore

* Export the results *
estout e1 e2 e3 e4 e5 e6 e7 e8 e9 using Tables/Table2pan5.tex, style(tex) varlabels(supplyShock "log of seizures in Colombia") cells(b(star fmt(%9.3f)) se(par fmt(%9.3f))) stats(N r2 , fmt(%7.0f  %7.3f) labels("Observations" "R-squared"))  collabels(none)  keep(supplyShock) order(supplyShock)  starlevels(\$^{*}\$ .1 \$^{**}\$ .05 \$^{***}\$ .01) nolabel replace mlabels(none)


 
